package com.xb.p32_sqlitedemo1;

import androidx.appcompat.app.AppCompatActivity;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;

public class MainActivity extends AppCompatActivity {
    Button btn_add,btn_update,btn_delete,btn_query;
    EditText et_username,et_password;
    TextView tv_show;

    MyDBHelper dbHelper;
    SQLiteDatabase sqldb;
    String sql;
    String uname=null;
    String upwd=null;
    //用来保存查询数据的游标
    Cursor cursor=null;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        btn_add=findViewById(R.id.add);
        btn_update=findViewById(R.id.update);
        btn_delete=findViewById(R.id.delete);
        btn_query=findViewById(R.id.query);

        et_username=findViewById(R.id.username);
        et_password=findViewById(R.id.password);
        tv_show=findViewById(R.id.tv_show);

        dbHelper=new MyDBHelper(this,"demo.db",null,1);
        sqldb=dbHelper.getWritableDatabase();
    }

    public void btnClick(View view){
        uname=et_username.getText().toString();
        upwd=et_password.getText().toString();

        switch(view.getId()){
            case R.id.add:
                sql="insert into user(username,password) values('"+uname+"','"+upwd+"');";
                sqldb.execSQL(sql);
                et_username.setText("");
                et_password.setText("");
                Toast.makeText(this,"插入一条数据："+uname+","+upwd,Toast.LENGTH_SHORT).show();
                break;
            case R.id.update:
                /*sql="update user set password='"+upwd+"' where username='"+uname+"'";
                sqldb.execSQL(sql);*/
                ContentValues values=new ContentValues();
                values.put("password",upwd);
                sqldb.update("user",values,"username=?",new String[]{uname});
                tv_show.append("更新了密码："+uname+","+upwd+"\n");
                break;
            case R.id.query:

                //查询指定内容
                sql="select * from user ";//如果需要拼接SQL语句的话，末尾一定要记得多一个空格
                String[] args=null;

                //拼接查询条件
                if(uname !=null && !uname.equals("")){
                    if(!uname.equals("all")){
                        sql=sql+"where username=?";
                        args=new String[]{uname};

                        if(upwd !=null && !upwd.equals("")){
                            sql=sql+"and password=?";
                            args=new String[]{uname,upwd};
                        }
                    }else {
                        args=null;
                    }
                    cursor=sqldb.rawQuery(sql,args);

                    /*//查询全部内容
                    sql="select * from user";
                    cursor=sqldb.rawQuery(sql,null);*/

                    cursor.moveToFirst();
                    int uid;String name=null;String passwd=null;
                    while (!cursor.isAfterLast()){
                        uid=cursor.getInt(cursor.getColumnIndex("userid"));
                        name=cursor.getString(cursor.getColumnIndex("username"));
                        passwd=cursor.getString(cursor.getColumnIndex("password"));
                        tv_show.append(uid+","+name+","+passwd+"\n");
                        cursor.moveToNext();
                    }
                }else{
                    Toast.makeText(this,"请输入用户名",Toast.LENGTH_SHORT).show();
                }

                break;
            case R.id.delete:
                /*sql="delete from user where username='"+uname+"'";
                sqldb.execSQL(sql);*/
               sqldb.delete("user","username=?",new String[]{uname});
                tv_show.append("删除了用户："+uname+"\n");
                break;
        }
    }
}